package com.turtledove.storyalbum.model.helper;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.turtledove.storyalbum.R;
import com.turtledove.storyalbum.common.UtilsCommon;

public class DBHelper extends SQLiteOpenHelper {

	private static SQLiteDatabase db;
	private final Context mContext;
	String fullPathDB;

	public DBHelper(Context context) {
		super(context, DBDefinition.DATABASE_NAME, null,
				DBDefinition.DATABASE_VERSION);
		this.mContext = context;
		File dbFile = mContext.getDatabasePath(DBDefinition.DATABASE_NAME);
		fullPathDB = dbFile.getAbsolutePath();
	}

	public void createDataBase() throws IOException {
		// If database not exists copy it from the assets
		if (checkDataBase() == false) {
			this.getReadableDatabase();
			try {
				// Copy the database from assests
				copyDataBase();
//				createAlbumDefault();
				Log.d("Luong", "=================Copy db thanh cong");
			} catch (IOException mIOException) {
				Log.d("Luong", "==============Copy db loi");

				throw new Error("ErrorCopyingDataBase");
			}
		}
	}

	void createAlbumDefault()
	{
		//Copy image to folder
		String url1 = UtilsCommon.copyFileAssets(mContext, "img1.png");
		String url2 = UtilsCommon.copyFileAssets(mContext, "img2.png");
		String url3 = UtilsCommon.copyFileAssets(mContext, "img3.png");
		Log.d("Luong", "Luong_______________ url1: "+url1);
		Log.d("Luong", "Luong_______________ url2: "+url2);
		Log.d("Luong", "Luong_______________ url3: "+url3);
		DBHandler.insertAlbum(mContext, mContext.getResources().getString(R.string.app_name), "1");
		int maxIdAlbum = DBHandler.getMaxIdAlbum(mContext);
		if (maxIdAlbum > 0) {
			if(url1.equals("") == false)
			{
				DBHandler.insertItem(mContext, url1, String.valueOf(maxIdAlbum), "1");
			}
			if(url2.equals("") == false)
			{
				DBHandler.insertItem(mContext, url2, String.valueOf(maxIdAlbum), "0");
			}
			if(url3.equals("") == false)
			{
				DBHandler.insertItem(mContext, url3, String.valueOf(maxIdAlbum), "0");
			}
		}
	}
	
	private boolean checkDataBase() {
		File dbFile = new File(fullPathDB);
		return dbFile.exists();
	}

	private void copyDataBase() throws IOException {
		InputStream mInput = mContext.getAssets().open(
				DBDefinition.DATABASE_NAME);
		File dbFile = mContext.getDatabasePath(DBDefinition.DATABASE_NAME);
		String outputPath = dbFile.getAbsolutePath();
		OutputStream mOutput = new FileOutputStream(outputPath);
		byte[] mBuffer = new byte[1024];
		int mLength;
		while ((mLength = mInput.read(mBuffer)) > 0) {
			mOutput.write(mBuffer, 0, mLength);
		}
		mOutput.flush();
		mOutput.close();
		mInput.close();
	}

	public boolean openDB() throws SQLException {
		db = SQLiteDatabase.openDatabase(fullPathDB, null,
				SQLiteDatabase.CREATE_IF_NECESSARY);
		return db != null;
	}

	@Override
	public synchronized void close() {
		if (db != null)
			db.close();
		super.close();
	}

	public void closeDB() {
//		db.close();
		close();
	}

	// ______________Item
	
	public Cursor getAllItem() {
		openDB();
		Cursor cursor = db.query(DBDefinition.TABLE_ITEM, new String[] {
				DBDefinition.COLUMN_ID, DBDefinition.COLUMN_IMAGE_URL,
				DBDefinition.COLUMN_ALBUM_ID, DBDefinition.COLUMN_ITEM_COVER },
				null, null, null, null, null);
		return cursor;
	}

	public Cursor getItemCoverByAlbumId(String albumid) {
		openDB();
		String where = DBDefinition.COLUMN_ALBUM_ID + " = " + albumid + " AND " + DBDefinition.COLUMN_ITEM_COVER + " = 1";
		Cursor cursor = db.query(DBDefinition.TABLE_ITEM, new String[] {
				DBDefinition.COLUMN_ID, DBDefinition.COLUMN_IMAGE_URL,
				DBDefinition.COLUMN_ALBUM_ID, DBDefinition.COLUMN_ITEM_COVER },
				where, null, null, null, null);
		return cursor;
	}
	
	public Cursor getItemByAlbumId(String albumid) {
		openDB();
		Cursor cursor = db.query(DBDefinition.TABLE_ITEM, new String[] {
				DBDefinition.COLUMN_ID, DBDefinition.COLUMN_IMAGE_URL,
				DBDefinition.COLUMN_ALBUM_ID, DBDefinition.COLUMN_ITEM_COVER },
				DBDefinition.COLUMN_ALBUM_ID + " = " + albumid, null, null, null, null);
		return cursor;
	}

	public Cursor getItemById(int id) {
		openDB();
		Cursor cursor = db.query(DBDefinition.TABLE_ITEM, new String[] {
				DBDefinition.COLUMN_ID, DBDefinition.COLUMN_IMAGE_URL,
				DBDefinition.COLUMN_ALBUM_ID, DBDefinition.COLUMN_ITEM_COVER },
				DBDefinition.COLUMN_ID + " = '" + id + "'", null, null, null,
				null);
		return cursor;
	}

	public boolean insertItem(String urlImage, String idAlbum, String itemCover) {
		openDB();
		ContentValues values = new ContentValues();
		values.put(DBDefinition.COLUMN_IMAGE_URL, urlImage);
		values.put(DBDefinition.COLUMN_ALBUM_ID, idAlbum);
		values.put(DBDefinition.COLUMN_ITEM_COVER, itemCover);
		return db.insert(DBDefinition.TABLE_ITEM, null, values) > -1;
	}

	public void updateItemCover(int id, String idAlbum, String itemCover) {
		openDB();
		ContentValues values = new ContentValues();
		if (itemCover != null) {
			values.put(DBDefinition.COLUMN_ITEM_COVER, "0");
		}
		db.update(DBDefinition.TABLE_ITEM, values, DBDefinition.COLUMN_ALBUM_ID
				+ " = '" + idAlbum + "'", null);
		
		values = new ContentValues();
		if (itemCover != null) {
			values.put(DBDefinition.COLUMN_ITEM_COVER, itemCover);
		}
		db.update(DBDefinition.TABLE_ITEM, values, DBDefinition.COLUMN_ID
				+ " = '" + id + "' AND " + DBDefinition.COLUMN_ALBUM_ID
				+ " = '" + idAlbum + "'", null);
		closeDB();
	}

	public void deleteItemById(int id) {
		openDB();
		db.delete(DBDefinition.TABLE_ITEM, DBDefinition.COLUMN_ID + " = '" + id
				+ "'", null);
		closeDB();
	}
	
	public void deleteItemByAlbumId(String albumid) {
		openDB();
		db.delete(DBDefinition.TABLE_ITEM, DBDefinition.COLUMN_ALBUM_ID + " = " + albumid, null);
		closeDB();
	}

	// _____________ Album

	public Cursor getAlbumCurrent() {
		openDB();
		Cursor cursor = db.query(DBDefinition.TABLE_ALBUM, new String[] {
				DBDefinition.COLUMN_ID, DBDefinition.COLUMN_NAME,
				DBDefinition.COLUMN_CURRENT_ALBUM }, DBDefinition.COLUMN_CURRENT_ALBUM
				+ " = 1", null, null, null, null);
		return cursor;
	}
	
	public Cursor getAlbumById(int id) {
		openDB();
		Cursor cursor = db.query(DBDefinition.TABLE_ALBUM, new String[] {
				DBDefinition.COLUMN_ID, DBDefinition.COLUMN_NAME,
				DBDefinition.COLUMN_CURRENT_ALBUM }, DBDefinition.COLUMN_ID
				+ " = '" + id + "'", null, null, null, null);
		return cursor;
	}

	public Cursor getAllAlbum() {
		openDB();
		Cursor cursor = db.query(DBDefinition.TABLE_ALBUM, new String[] {
				DBDefinition.COLUMN_ID, DBDefinition.COLUMN_NAME,
				DBDefinition.COLUMN_CURRENT_ALBUM }, null, null, null, null,
				null);
		return cursor;
	}

	public boolean insertAlbum(String nameAlbum, String currentAlbum) {
		openDB();
		ContentValues values = new ContentValues();
		values.put(DBDefinition.COLUMN_NAME, nameAlbum);
		values.put(DBDefinition.COLUMN_CURRENT_ALBUM, currentAlbum);
		return db.insert(DBDefinition.TABLE_ALBUM, null, values) > -1;
	}

	public void updateAlbum(int id, String nameAlbum, String currentAlbum) {
		openDB();
		ContentValues values = new ContentValues();
		if (nameAlbum != null) {
			values.put(DBDefinition.COLUMN_NAME, nameAlbum);
		}
		if (currentAlbum != null) {
			values.put(DBDefinition.COLUMN_CURRENT_ALBUM, currentAlbum);
		}
		db.update(DBDefinition.TABLE_ALBUM, values, DBDefinition.COLUMN_ID
				+ " = '" + id + "'", null);
		closeDB();
	}
	
	public void disableCurrentAlbum() {
		openDB();
		ContentValues values = new ContentValues();
		values.put(DBDefinition.COLUMN_CURRENT_ALBUM, "0");
		db.update(DBDefinition.TABLE_ALBUM, values, null, null);
		closeDB();
	}

	public void deleteAlbum(int id) {
		openDB();
		db.delete(DBDefinition.TABLE_ALBUM, DBDefinition.COLUMN_ID + " = '"
				+ id + "'", null);
		closeDB();
	}

	// ____________________Interval
	public Cursor getInterval() {
		openDB();
		Cursor cursor = db.query(DBDefinition.TABLE_INTERVAL,
				new String[] { DBDefinition.COLUMN_INTERVAL },
				DBDefinition.COLUMN_ID + " = '1'", null, null, null, null);
		return cursor;
	}

	public void updateInterval(String interval) {
		openDB();
		ContentValues values = new ContentValues();
		if (interval != null) {
			values.put(DBDefinition.COLUMN_INTERVAL, interval);
		}
		db.update(DBDefinition.TABLE_INTERVAL, values, DBDefinition.COLUMN_ID
				+ " = '1'", null);
		closeDB();
	}

	@Override
	public void onCreate(SQLiteDatabase arg0) {
		// TODO Auto-generated method stub

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub

	}

}
